import DBConnect
import getBendibaoNews
from pymysql.cursors import DictCursor
import json
import datetime


def insert_to_db(datalist):
    db = DBConnect.connect()

    cursor = db.cursor()
    for data in datalist:
        sql = "SELECT COUNT(*) FROM news WHERE title = \'{}\'".format(data['title'])
        cursor.execute(sql)
        s = cursor.fetchall()[0][0]

        if s == 0:
            sql = ("INSERT INTO news(title, link, time, origin, image, contents) VALUES (\"{}\", \"{}\", \"{}\", \"{}\", \"{}\", \"{}\")". \
                format(data['title'], data['link'], data['time'], data['origin'], data['imgUrl'], data['content']))
            try:
                cursor.execute(sql)
                db.commit()
            except:
                db.rollback()

    db.close()
    

def get_all_news():
    db = DBConnect.connect()

    cursor = db.cursor(DictCursor)
    sql = ("SELECT * FROM news")
    cursor.execute(sql)
    data = cursor.fetchall()
    data = [d for d in data if (datetime.datetime.now() - datetime.datetime(int(d['time'].split('-')[0]), int(d['time'].split('-')[1]), int(d['time'].split('-')[2]))).days < 60]
    data = sorted(data, key=lambda d:datetime.date(int(d['time'].split('-')[0]), int(d['time'].split('-')[1]), int(d['time'].split('-')[2])), reverse=True)
    data = json.dumps(data, ensure_ascii=False)
    db.close()
    return data


def update_db():
    getBendibaoNews.get_data()
    data_list = json.load(open("../data/BendibaoNews.json", encoding="utf-8"))
    insert_to_db(data_list)


if __name__ == "__main__":
    print(get_all_news())
    pass